﻿# -*- coding: UTF-8 -*-
import pymysql


# Mysql增删改查
class Mysql_:
    Login = None
    data = None
    __sql = None
    __run = None
    __id = None

    # 构s造初始化
    def __init__(self, sql=None):
        self.Login = pymysql.connect(
            host="localhost",
            user="root", password="123",
            db="mydb", charset="utf8"
        )  # 获取数据库连接

    # fetchall查询
    def selects(self):
        print(
            " " * 5, "|{:^1}|{:^9}|{:^3}|{:^2}|{:^9}|".format(
                '序号',
                '姓名',
                '年龄',
                '性别',
                '班级'
            ))
        try:
            cusor = self.Login.cursor()  # 初始化游标
            cusor.execute("select * from stu")  # 执行查询
            self.data = cusor.fetchall()
            for __ in self.data:
                print(" " * 5, "|%-4d|%-10s|%-4d|%-4s|%-10s|" % (__))
            self.Login.commit()  # 事务提交
        except Exception as err:
            self.Login.rollback()  # 事务回滚
            print("SQL执行错误!原因", err)

    # fetchone_id查询
    def select_id(self, __id):
        try:
            cusor = self.Login.cursor()  # 初始化游标
            cusor.execute("select id from stu where id = '%d'" %
                          (__id))  # 执行查询
            return cusor.fetchone()
            self.Login.commit()  # 事务提交
        except Exception as err:
            self.Login.rollback()  # 事务回滚
            print("SQL执行错误!原因", err)

    # fetchone_name查询
    def select(self, name):
        try:
            cusor = self.Login.cursor()  # 初始化游标
            cusor.execute("select id from stu where name = '%s'" %
                          (name))  # 执行查询
            return cusor.fetchone()
            self.Login.commit()  # 事务提交
        except Exception as err:
            self.Login.rollback()  # 事务回滚
            print("SQL执行错误!原因", err)

    # 添加
    def insert(self, data):

        try:
            cusor = self.Login.cursor()  # 初始化游标
            __run = cusor.execute(
                "insert into stu(name,age,sex,classid) values('%s','%d','%s','%s')" % (data))  # 执行添加
            if __run == 1:
                print('姓名:%s,年龄:%d,性别%s,班级%s' % (data), '成功添加!')
            self.Login.commit()  # 事务提交
        except Exception as err:
            self.Login.rollback()  # 事务回滚
            print("SQL执行错误!原因", err)

    # 修改
    def updata(self, data):

        try:
            cusor = self.Login.cursor()  # 初始化游标
            __run = cusor.execute(
                "update stu set name='%s',age='%d',sex='%s',classid='%s' where id='%d'" % (data))  # 执行添加
            if __run == 1:
                print('姓名:%s,年龄:%d,性别%s,班级%s,序号:%d,' % (data), '成功修改!')
            self.Login.commit()  # 事务提交
        except Exception as err:
            self.Login.rollback()  # 事务回滚
            print("SQL执行错误!原因", err)

    # 删除
    def delete(self, __id):

        try:
            cusor = self.Login.cursor()  # 初始化游标
            __run = cusor.execute(
                "delete from stu where id=%d" % (__id))  # 执行添加
            if __run == 1:
                print('信息成功删除!')

            self.Login.commit()  # 事务提交
        except Exception as err:
            self.Login.rollback()  # 事务回滚
            print("SQL执行错误!原因", err)

    def __del__(self):  # 析构
        self.Login.close()  # 关闭数据库


# 主菜单
def _page():
    print('┄--={}{}=--┄'.format('=' * 15, '学员信息在线管理系统' + '=' * 15))
    print('{:>20}{:^10}{}'.format('1. 查看学员信息', '', '2. 添加学员信息'))
    print('{:>20}{:^10}{}'.format('3. 修改学员信息', '', '4. 删除学员信息'))
    print('{:>20}{:^10}{}'.format('5. 退出关闭系统', '', ''))


db = Mysql_(False)
Menu = '0'

# 功能执行
while True:
    if Menu == '0':  # 首页
        print('\n' * 20)
        _page()
        Menu = input('请输入对应数字选择:')

    elif Menu == '1':  # 查询
        print('┄--={}{}=--┄'.format('=' * 15, ' 学 员 信 息 浏 览 ' + '=' * 15))
        db.selects()
        input('按任意键回到首页')
        Menu = '0'

    elif Menu == '2':  # 添加
        print('\n' * 20, '┄--={}{}=--┄'.format('=' * 15, ' 学 员 信 息 添 加 ' + '=' * 15))
        db.selects()
        cc1 = input('请输入姓名:')
        while db.select(cc1):
            cc1 = input(cc1 + '已存在,请重新输入:')
        cc2 = input('请输入年龄:')
        while cc2.isdigit() is False:

            cc2 = input('格式错误,请重新输入年龄:')
        while True:
            cc3 = input('请输入性别w/m:')
            if cc3 == 'w' or cc3 == 'm':
                break
        cc4 = input('请输入班级:')
        db.data = (cc1, int(cc2), cc3, cc4)
        db.insert(db.data)
        cc5 = input('输入回车继续添加,输入0返回首页!')
        if cc5 == '0':
            Menu = cc5
    elif Menu == '3':  # 修改
        print('┄--={}{}=--┄'.format('=' * 15, ' 学 员 信 息 修 改 ' + '=' * 15))
        db.selects()
        cc0 = input('请输入需要修改信息人的id:')
        while cc0.isdigit() is False:
            cc0 = input('格式错误,请重新输入需要修改信息人的id:')
        while db.select_id(int(cc0)) is None:
            cc0 = input(cc0 + '不存在,请重新输入:')
        cc0 = db.select_id(int(cc0))[0]
        cc1 = input('请输入姓名:')
        while db.select(cc1):
            cc1 = input(cc1 + '已存在,请重新输入:')
        cc2 = input('请输入年龄:')
        while cc2.isdigit() is False:
            cc2 = input('格式错误,请重新输入年龄:')
        while True:
            cc3 = input('请输入性别w/m:')
            if cc3 == 'w' or cc3 == 'm':
                break
        cc4 = input('请输入班级:')
        db.data = (cc1, int(cc2), cc3, cc4, cc0)
        db.updata(db.data)
        cc5 = input('输入回车继续修改,输入0返回首页!')
        if cc5 == '0':
            Menu = cc5
    elif Menu == '4':  # 删除
        print('┄--={}{}=--┄'.format('=' * 15, ' 学 员 信 息 删 除 ' + '=' * 15))
        db.selects()
        cc0 = input('请输入需要删除信息人的id:')
        while cc0.isdigit() is False:
            cc0 = input('格式错误,请重新输入需要删除信息人的id:')
        while db.select_id(int(cc0)) is None:
            cc0 = input(cc0 + '不存在,请重新输入:')
        cc0 = db.select_id(int(cc0))[0]
        db.delete(cc0)
        cc5 = input('输入回车继续删除,输入0返回首页!')
        if cc5 == '0':
            Menu = cc5
    elif Menu == '5':
        print('\n' * 20, '┄--={}{}=--┄'.format('=' * 15, ' 再 见 ! ' + '=' * 15), '\n' * 5)
        input()
    else:
        input('无效的输入!按任意键返回首页.')
        Menu = '0'
